package com.elite.groovy.callback

import com.elite.groovy.kh.*
import com.elite.groovy.util.EliteBean
import com.elite.groovy.util.FileUploadUtil
import com.elite.groovy.util.Util
import com.wisdge.dataservice.Result
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.lang.StringEscapeUtils
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import java.text.SimpleDateFormat
import java.util.regex.Matcher
import java.util.regex.Pattern

/**
 * @projectName: ngsapi_cs
 * @package: cn.com.callback
 * @className: apiDataExport
 * @author: LiJT
 * @description: 数据导出服务
 * @date: 2022-12-19 14:03
 * @version: 1.0
 */

public class apiDataCallExport implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiDataCallExport.class);
    private static String out_DomainURL = "";
    private static String in_DomainURL = "";
    private static String TempFilesPath = "";
    private static String tmp_FileDir = "";
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        long startTime = System.currentTimeMillis();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");

        String callBody = ""; // 入参请求体
        JSONObject resJson = new JSONObject();
        String export_type = ""; // 导出类型

        try {

            // 获取系统对外域名
            out_DomainURL = KHUtil.isNull(out_DomainURL) ? KHDBConfig.getAPIURL(eliteBean, "SYS-OuterDomain") : out_DomainURL;
            out_DomainURL = out_DomainURL.endsWith("/") ? out_DomainURL : out_DomainURL + "/";

            in_DomainURL = KHUtil.isNull(in_DomainURL) ? KHDBConfig.getAPIURL(eliteBean, "SYS-InnerDomain") : in_DomainURL;
            in_DomainURL = in_DomainURL.endsWith("/") ? in_DomainURL : in_DomainURL + "/";

            TempFilesPath = KHUtil.isNull(TempFilesPath) ? KHDBConfig.getParamConfig(eliteBean, "SYS_TempFilesPath") : TempFilesPath;
            TempFilesPath = TempFilesPath.endsWith("/") ? TempFilesPath : TempFilesPath + "/";

            // 调用类型校验,此接口仅支持GET
            String getMethod = request.getMethod();
            log.debug("getMethod:" + getMethod);
            if (!getMethod.equals("POST")) {
                log.error("HTTP请求调用类型错误，接口调用失败：" + getMethod);
                return errorRedirect(response, "调用类型异常", "HTTP请求调用类型错误，接口调用失败：" + getMethod);
            }

            // 获取所有Header
            Map<String, String> mapHeader = new HashMap<String, String>();
            Enumeration headerNames = request.getHeaderNames();
            while (headerNames.hasMoreElements()) {
                String key = (String) headerNames.nextElement();
                String value = request.getHeader(key);
                log.debug("Call Header-" + key + ":" + value);

                mapHeader.put(key, value);
            }

            // 获取所有参数名
            Map<String, String> mapParams = new HashMap<String, String>();
            Enumeration ep = request.getParameterNames();
            while (ep.hasMoreElements()) {
                String key = (String) ep.nextElement();
                String value = request.getParameter(key);
                log.debug("Call Parameter-" + key + ":" + value);
                mapParams.put(key, value);

            }

            // 获取调用数据
            callBody = Util.getRequestBody(request, "UTF-8");
            log.info("接收调用 POST 参数信息：" + callBody);

            // 获取调用报文
            JSONObject callJson = JSONObject.fromObject(callBody);

            // 获取导出类型
            export_type = KHUtil.GetSFJV(callJson, "export_type"); // 导出类型

            if (export_type == "WO") {
                resJson = doWOExport(eliteBean, response, callJson);

            } else if (export_type == "WO-FWYC") {
                resJson = doWOExport_FWYC(eliteBean, response, callJson);

            } else if (export_type == "CHATHIS") {
                resJson = doCHATHisExport(eliteBean, response, callJson);

            } else {
                resJson = errorRedirect(response, "操作类型异常", "不支持的操作类型export_type:" + export_type);

            }
        } catch (Exception e) {
            log.error(Util.getERRO(e));
            resJson = errorRedirect(response, "全局处理异常", "全局处理异常:" + e.getMessage());
        } finally {
            LogToDB.toDB(eliteBean, "dataSource", "数据导出服务[Excel]", "apiDataExport", callBody, resJson.toString(), "本次发送总耗时：" + (System.currentTimeMillis() - startTime).toString(), export_type);

        }

        return resJson;

    }

    // 工单数据导出
    public static JSONObject doWOExport(EliteBean eliteBean, HttpServletResponse response, JSONObject callJson) {

        tmp_FileDir = "工单数据_" + sdf.format(new Date());

        // 获取数据源List<Map<String, Object>>
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        log.debug("初次嵌入请求 返回Html页面参数信息【callJson】：" + callJson);

        JSONArray exportArr = callJson.getJSONArray("data");
        String fieldsInfo = KHUtil.GetSFJV(callJson, "fields");
        String[] fieldsArr = fieldsInfo.split(";");
        List<String> headFields = new ArrayList<>();

        List<LinkedHashMap<String, Object>> mapArrayList = new ArrayList<>();

        // Init工单数据明细SQL
        String exportListSQL = '''Select R1.Workorderid,
                                   R1.Createddate,
                                   So.Objectivestatusdescription,
                                   R3.Workordertype2desc,
                                   Cu.Customername,
                                   Cu.Mobile,
                                   Cc1.Code_Name,
                                   Ccp.Province_Desc,
                                   Cct.City_Desc,
                                   Cca.Area_Desc,
                                   R1.Fakes_Address_Details,
                                   R1.Fakes_Store,
                                   R1.Fakes_Account,
                                   R1.Fakes_Link,
                                   R1.Remark
                              From Tabcworkorder R1
                              Left Join Objective Obj
                                On R1.Relation_Id = Obj.Objective_Guid
                              Left Join Sys_Workordertype2 R3
                                On R1.Workordertype2 = R3.Workordertype2_Id
                              Left Join Customer Cu
                                On R1.Customer_Guid = Cu.Customer_Guid
                              Left Join Sysc_Objectivestatus So
                                On Obj.Objectivestatus = So.Objectivestatus
                              Left Join c_Codetables Cc1
                                On Cc1.Use_Type = 'salechannel'
                               And Cc1.Use_Status = '1'
                               And Cc1.Code_Id = R1.Salechannel_Id
                              Left Join c_Province Ccp
                                On R1.Province = Ccp.Province_Id
                              Left Join c_City Cct
                                On R1.City = Cct.City_Id
                              Left Join c_Area Cca
                                On R1.Area = Cca.Area_Id
                             Where R1.Workorderid = ?''';

        // Init工单是否有附件SQL
        String attachListSQL = '''Select Up.Uploadguid,
                                           Up.Path,
                                           Up.Filename,
                                           Up.Filedesc,
                                           R1.Upload_Class1_Desc,
                                           R2.Upload_Class2_Desc
                                      From Uploadlist Up
                                      Left Join Sysc_Upload_Class1 R1
                                        On Up.Upload_Class1_Id = R1.Upload_Class1_Id
                                      Left Join Sysc_Upload_Class2 R2
                                        On R1.Upload_Class1_Id = R2.Upload_Class1_Id
                                       And Up.Upload_Class2_Id = R2.Upload_Class2_Id
                                     Where Up.Guid = ?
                                     Order By Up.Upload_Class2_Id''';

        for (int i = 0; i < exportArr.size(); i++) {
            String Workorderid = exportArr.getString(i);

            // 查询工单明细数据
            List<Map<String, Object>> woDataList = jt.queryForList(exportListSQL, Workorderid);

            // 记录工单明细数据 Iterator迭代器
            Iterator<Map<String, Object>> it = woDataList.iterator();
            while (it.hasNext()) {
                Map<String, Object> dataMap = it.next();

                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
                    System.out.println(entry.getKey() + ":" + entry.getValue());
                    map.put(entry.getKey(), entry.getValue());

                    // 如果是第一条数据则将列头
                    if (i == 0) {
                        headFields.add(entry.getKey());
                    }
                }
                mapArrayList.add(map);
            }

            // 查询工单附件数据
            List<Map<String, Object>> attachList = jt.queryForList(attachListSQL, Workorderid);

            int attach_Seq = 0;
            String old_Class_Desc = "";
            for (int j = 0; j < attachList.size(); j++) {
                Map<String, Object> attachData = attachList.get(j);
                String Uploadguid = attachData.get("Uploadguid");
                String Path = attachData.get("Path");
                String Filename = attachData.get("Filename");
                String Upload_Class1_Desc = attachData.get("Upload_Class1_Desc");
                String Upload_Class2_Desc = attachData.get("Upload_Class2_Desc");

                // 文件序号处理
                if (old_Class_Desc != Upload_Class1_Desc + "_" + Upload_Class2_Desc) {
                    attach_Seq = 0;
                    old_Class_Desc = Upload_Class1_Desc + "_" + Upload_Class2_Desc
                }
                attach_Seq = attach_Seq + 1;

                // 生成文件名
                String save_FileName = Upload_Class1_Desc + "_" + Upload_Class2_Desc + "[" + attach_Seq + "]" + KHUtil.getIntRandom(6) + "-" + Filename;
                String save_FileDir = TempFilesPath + tmp_FileDir + "/附件(" + (i + 1) + ")-" + Workorderid + "_v" + tmp_FileDir.replace("工单数据_", "") + "/";
                String save_FilePath = save_FileDir + save_FileName;

                // 临时目录检查&创建
                Util.createDir(save_FileDir);

                // 下载文件
                boolean downFlag = false;
                try {
                    downFlag = Util.httpDownLoadFile(in_DomainURL + "ngs/fs/get?file=" + Path, save_FilePath, 12000);
                    log.info("附件-" + j + "文件下载成功！...");

                } catch (Exception e) {
                    log.error("附件-" + j + "文件下载到服务器-处理异常");
                }

            }

        }

        // 导出Excel列头生成
        List<String> head = new ArrayList<>();
        if (KHUtil.isNull(fieldsInfo)) {
            head = headFields;
        } else {
            Arrays.stream(fieldsArr).forEach(element -> {
                // 处理每个元素
                head.add(element);
            });
        }
        log.debug("导出Excel列头生成:" + head);

        try {
            // 生成Excel文件
            String exportPath = KHExcelUtil.getInstance().exportExcel(mapArrayList, head, TempFilesPath + tmp_FileDir + "/", "工单数据清单-v" + tmp_FileDir.replace("工单数据_", ""));
            log.debug("导出Excel存储路径:" + exportPath);

            // 压缩文件夹Zip
            String zipFilePath = TempFilesPath + tmp_FileDir + "/" + tmp_FileDir + ".zip";
            String doLinuxCmd = "cd " + TempFilesPath + tmp_FileDir + System.lineSeparator() + "rm -rf " + zipFilePath + System.lineSeparator() + "zip -r " + tmp_FileDir + ".zip *";
            log.debug("压缩文件执行命令:" + doLinuxCmd);

            KHFileUtil.doLinuxCmd(doLinuxCmd);
            log.debug("压缩文件存储路径:" + zipFilePath);

            // 上传压缩包
            File tempFile = new File(zipFilePath.trim());
            String uploadResult = FileUploadUtil.uploadFile(in_DomainURL + "ngs/", KHDBConfig.getParamConfig(eliteBean, "NGS_fileServerKey"), "/export", tempFile);
            log.debug("文件上传NGS结果：" + uploadResult);

            JSONObject resultJson = JSONObject.fromObject(uploadResult);

            String code = KHUtil.GetSFJV(resultJson, "code");
            if (code.equals("1")) {
                return JSONObject.fromObject(new Result(Result.SUCCESS, resultJson.getJSONArray("value").getString(0), out_DomainURL + "ngs/fs/get?file=" + resultJson.getJSONArray("value").getString(0)));
            } else {
                return errorRedirect(response, "文件上传NGS异常", "文件上传NGS结果异常:" + KHUtil.GetSFJV(resultJson, "message"));
            }

        } catch (Exception e) {
            log.error("Excel文件处理异常", e);
            return errorRedirect(response, "Excel文件处理异常", "Excel文件处理异常:" + e.getMessage());
        } finally {
            boolean del_flag = KHFileUtil.deleteDiretory(TempFilesPath + tmp_FileDir);
            log.error("End临时文件删除结果:" + del_flag);

        }

    }

    // 工单数据导出(防伪异常)
    public static JSONObject doWOExport_FWYC(EliteBean eliteBean, HttpServletResponse response, JSONObject callJson) {

        tmp_FileDir = "工单数据_防伪异常_" + sdf.format(new Date());

        // 获取数据源List<Map<String, Object>>
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        log.debug("初次嵌入请求 返回Html页面参数信息【callJson】：" + callJson);

        JSONArray exportArr = callJson.getJSONArray("data");
        String fieldsInfo = KHUtil.GetSFJV(callJson, "fields");
        String[] fieldsArr = fieldsInfo.split(";");
        List<String> headFields = new ArrayList<>();

        List<LinkedHashMap<String, Object>> mapArrayList = new ArrayList<>();

        // Init工单数据明细SQL
        String exportListSQL = '''select r1.workorderid,
       r1.createddate,
       so.objectivestatusdescription,
       r3.workordertype2desc,
       cu.customername,
       cu.mobile,
       -- 第一组产品
       cc1.code_name as 1_购买渠道,
       cd1.code_name as 1_异常情况说明,
       p1.security_info as 1_其他异常说明,
       p1.store_name as 1_店铺名称,
       p1.store_address as 1_店铺地址,
       concat(ccp1.province_desc, cct1.city_desc, cca1.area_desc) as 1_所属区域省市区,
       ce1.code_name as 1_线上渠道,
       p1.purchasing_mobile as 1_购货手机,
       p1.other_commerce_channel as 1_其他渠道说明,
       p1.security_label as 1_防伪标签_img,
       p1.manufacture_img as 1_生产日期_img,
       p1.packing_front as 1_外包装正面_img,
       p1.packing_opposite as 1_外包装反面_img,
       -- 第二组产品
       cc2.code_name as 2_购买渠道,
       cd2.code_name as 2_异常情况说明,
       p2.security_info as 2_其他异常说明,
       p2.store_name as 2_店铺名称,
       p2.store_address as 2_店铺地址,
       concat(ccp2.province_desc, cct2.city_desc, cca2.area_desc) as 2_所属区域省市区,
       ce2.code_name as 2_线上渠道,
       p2.purchasing_mobile as 2_购货手机,
       p2.other_commerce_channel as 2_其他渠道说明,
       p2.security_label as 2_防伪标签_img,
       p2.manufacture_img as 2_生产日期_img,
       p2.packing_front as 2_外包装正面_img,
       p2.packing_opposite as 2_外包装反面_img,
       -- 第三组产品
       cc3.code_name as 3_购买渠道,
       cd3.code_name as 3_异常情况说明,
       p3.security_info as 3_其他异常说明,
       p3.store_name as 3_店铺名称,
       p3.store_address as 3_店铺地址,
       concat(ccp3.province_desc, cct3.city_desc, cca3.area_desc) as 3_所属区域省市区,
       ce3.code_name as 3_线上渠道,
       p3.purchasing_mobile as 3_购货手机,
       p3.other_commerce_channel as 3_其他渠道说明,
       p3.security_label as 3_防伪标签_img,
       p3.manufacture_img as 3_生产日期_img,
       p3.packing_front as 3_外包装正面_img,
       p3.packing_opposite as 3_外包装反面_img

  from tabcworkorder r1
  left join objective obj
    on r1.relation_id = obj.objective_guid
  left join sys_workordertype2 r3
    on r1.workordertype2 = r3.workordertype2_id
  left join customer cu
    on r1.customer_guid = cu.customer_guid
  left join sysc_objectivestatus so
    on obj.objectivestatus = so.objectivestatus

-- 第一组产品
  left join dg_security p1
    on r1.relation_id = p1.objective_guid
   and p1.add_no = 1
  left join c_codetables cc1
    on cc1.use_type = 'purchase_channels'
   and cc1.code_id = p1.commerce_channel
  left join c_codetables cd1
    on cd1.use_type = 'security_type'
   and cd1.code_id = p1.security_type
  left join c_codetables ce1
    on ce1.use_type = 'commerce_channel'
   and ce1.code_id = p1.online_channels
  left join c_province ccp1
    on p1.province = ccp1.province_id
  left join c_city cct1
    on p1.city = cct1.city_id
  left join c_area cca1
    on p1.area = cca1.area_id

-- 第二组产品
  left join dg_security p2
    on r1.relation_id = p2.objective_guid
   and p2.add_no = 2
  left join c_codetables cc2
    on cc2.use_type = 'purchase_channels'
   and cc2.code_id = p2.commerce_channel
  left join c_codetables cd2
    on cd2.use_type = 'security_type'
   and cd2.code_id = p2.security_type
  left join c_codetables ce2
    on ce2.use_type = 'commerce_channel'
   and ce2.code_id = p2.online_channels
  left join c_province ccp2
    on p2.province = ccp2.province_id
  left join c_city cct2
    on p2.city = cct2.city_id
  left join c_area cca2
    on p2.area = cca2.area_id

-- 第三组产品
  left join dg_security p3
    on r1.relation_id = p3.objective_guid
   and p3.add_no = 3
  left join c_codetables cc3
    on cc3.use_type = 'purchase_channels'
   and cc3.code_id = p3.commerce_channel
  left join c_codetables cd3
    on cd3.use_type = 'security_type'
   and cd3.code_id = p3.security_type
  left join c_codetables ce3
    on ce3.use_type = 'commerce_channel'
   and ce3.code_id = p3.online_channels
  left join c_province ccp3
    on p3.province = ccp3.province_id
  left join c_city cct3
    on p3.city = cct3.city_id
  left join c_area cca3
    on p3.area = cca3.area_id
 where r1.workorderid = ?''';

        // Init工单-防伪查询产品组
        String initAdd_No_SQL = '''update dg_security r,
       (select r.id,
               r.objective_guid,
               case
                 when @last_class = r.objective_guid then
                  @rank := @rank + 1
                 else
                  @rank := 1
               end as ranks,
               @last_class := r.objective_guid as last_class,
               @last_id := r.id as last_id
          from objective o, dg_security r, (select @last_class := '', @last_id := '', @rank := 0) t
         where o.objective_guid = r.objective_guid
           and o.workorderid = ?
         order by r.objective_guid, r.id) r1
   set r.add_no = r1.ranks
 where r.objective_guid = r1.objective_guid
   and r.id = r1.id
   and r.add_no = 0''';

        for (int i = 0; i < exportArr.size(); i++) {
            String Workorderid = exportArr.getString(i);

            // Init工单-防伪查询产品组
            jt.update(initAdd_No_SQL, Workorderid);

            // 查询工单明细数据
            List<Map<String, Object>> woDataList = jt.queryForList(exportListSQL, Workorderid);

            // 记录工单明细数据 Iterator迭代器
            Iterator<Map<String, Object>> it = woDataList.iterator();
            while (it.hasNext()) {
                Map<String, Object> dataMap = it.next();

                LinkedHashMap<String, Object> fieldMap = new LinkedHashMap<>();
                // 查询工单附件数据
                List<Map<String, Object>> attachList = new ArrayList<Map<String, Object>>();
                for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
                    log.debug("工单明细数据字段 " + entry.getKey() + ":" + entry.getValue());
                    Map<String, Object> attacMap = new HashMap<String, Object>();

                    String file_Path = entry.getValue().toString();
                    if (entry.getKey().endsWith("_img")) {

                        if (!KHUtil.isNull(file_Path) && file_Path != "null") {
                            attacMap.put("Path", file_Path);
                            attacMap.put("Filename", file_Path.substring(file_Path.lastIndexOf('/') + 1));
                            attacMap.put("Upload_Class1_Desc", "防伪异常");
                            attacMap.put("Upload_Class2_Desc", entry.getKey());

                            attachList.add(attacMap);
                        }
                    } else {
                        fieldMap.put(entry.getKey(), entry.getValue());

                        // 如果是第一条数据则将列头
                        if (i == 0) {
                            headFields.add(entry.getKey());
                        }
                    }
                }
                mapArrayList.add(fieldMap);

                downAcctchFiles(Workorderid, i, attachList);
            }

        }

        // 导出Excel列头生成
        List<String> head = new ArrayList<>();
        if (KHUtil.isNull(fieldsInfo)) {
            head = headFields;
        } else {
            Arrays.stream(fieldsArr).forEach(element -> {
                // 处理每个元素
                head.add(element);
            });
        }
        log.debug("导出Excel列头生成:" + head);

        try {
            // 生成Excel文件
            String exportPath = KHExcelUtil.getInstance().exportExcel(mapArrayList, head, TempFilesPath + tmp_FileDir + "/", "工单数据清单-v" + tmp_FileDir.replace("工单数据_", ""));
            log.debug("导出Excel存储路径:" + exportPath);

            // 压缩文件夹Zip
            String zipFilePath = TempFilesPath + tmp_FileDir + "/" + tmp_FileDir + ".zip";
            String doLinuxCmd = "cd " + TempFilesPath + tmp_FileDir + System.lineSeparator() + "rm -rf " + zipFilePath + System.lineSeparator() + "zip -r " + tmp_FileDir + ".zip *";
            log.debug("压缩文件执行命令:" + doLinuxCmd);

            KHFileUtil.doLinuxCmd(doLinuxCmd);
            log.debug("压缩文件存储路径:" + zipFilePath);

            // 上传压缩包
            File tempFile = new File(zipFilePath.trim());
            String uploadResult = FileUploadUtil.uploadFile(in_DomainURL + "ngs/", KHDBConfig.getParamConfig(eliteBean, "NGS_fileServerKey"), "/export", tempFile);
            log.debug("文件上传NGS结果：" + uploadResult);

            JSONObject resultJson = JSONObject.fromObject(uploadResult);

            String code = KHUtil.GetSFJV(resultJson, "code");
            if (code.equals("1")) {
                return JSONObject.fromObject(new Result(Result.SUCCESS, resultJson.getJSONArray("value").getString(0), out_DomainURL + "ngs/fs/get?file=" + resultJson.getJSONArray("value").getString(0)));
            } else {
                return errorRedirect(response, "文件上传NGS异常", "文件上传NGS结果异常:" + KHUtil.GetSFJV(resultJson, "message"));
            }

        } catch (Exception e) {
            log.error("Excel文件处理异常", e);
            return errorRedirect(response, "Excel文件处理异常", "Excel文件处理异常:" + e.getMessage());
        } finally {
            boolean del_flag = KHFileUtil.deleteDiretory(TempFilesPath + tmp_FileDir);
            log.error("End临时文件删除结果:" + del_flag);

        }

    }

    // 网聊历史数据导出
    public static JSONObject doCHATHisExport(EliteBean eliteBean, HttpServletResponse response, JSONObject callJson) {

        log.debug("网聊历史数据_参数信息【callJson】：" + callJson);


        JSONArray headArr = callJson.getJSONArray("fields");
        JSONArray dataArr = callJson.getJSONArray("data");

        int headNum = headArr.size();
        int dataNum = dataArr.size();

        // 表头
        Map<String, String> headMap = new HashMap<String, String>();
        List<String> headList = new ArrayList<>();
        for (int i = 0; i < headNum; i++) {
            // 表头
            String colName = KHUtil.GetSFJV(headArr.getJSONObject(i), "colName");
            String display = KHUtil.GetSFJV(headArr.getJSONObject(i), "display");
            String width = KHUtil.GetSFJV(headArr.getJSONObject(i), "width");
            String realCol = KHUtil.GetSFJV(headArr.getJSONObject(i), "realCol");
            String editable = KHUtil.GetSFJV(headArr.getJSONObject(i), "editable");
            log.debug("表头字段过滤结果 colName:" + colName + " display:" + display + " width:" + width + " realCol:" + realCol + " editable:" + editable);

            // 表头字段过滤
            if (KHUtil.isNull(colName) && !KHUtil.isNull(display) && !Objects.equals(width, "0") && (Objects.equals(editable, "0") || KHUtil.isNull(editable))) {
                headMap.put(display, realCol);
                headList.add(display);
            }
        }
        int fieldsNum = headList.size();
        log.debug("表头字段过滤结果:" + headMap);

        headList.add("聊天记录(明细)")
        log.debug("导出Excel列头生成:" + headList);

        // 数据列
        List<LinkedHashMap<String, Object>> dataList = new ArrayList<>();

        for (int i = 0; i < dataNum; i++) {
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            JSONArray dataRow = dataArr.getJSONArray(i);

            for (int j = 0; j <= fieldsNum; j++) {
                if (j < fieldsNum) {
                    String headField = headList.get(j);
                    String headCol = headMap.get(headField);

                    map.put("Col_" + (j + 1), dataRow.getString(Integer.parseInt(headCol)));
                } else {

                    // 获取聊天历史
                    map.put("Col_" + (j + 1), getChat_Msg(eliteBean, dataRow.getString(0)));
                }
            }
            dataList.add(map);
        }

        try {
            // 生成Excel文件
            String exportPath = KHExcelUtil.getInstance().exportExcel(dataList, headList, TempFilesPath + tmp_FileDir + "/", "网聊历史数据清单-v" + tmp_FileDir.replace("网聊历史数据_", ""));
            log.debug("导出Excel存储路径:" + exportPath);

            // 上传压缩包
            File tempFile = new File(exportPath.trim());
            String uploadResult = FileUploadUtil.uploadFile(in_DomainURL + "ngs/", KHDBConfig.getParamConfig(eliteBean, "NGS_fileServerKey"), "/export", tempFile);
            log.debug("文件上传NGS结果：" + uploadResult);

            JSONObject resultJson = JSONObject.fromObject(uploadResult);

            String code = KHUtil.GetSFJV(resultJson, "code");
            if (code.equals("1")) {
                return JSONObject.fromObject(new Result(Result.SUCCESS, resultJson.getJSONArray("value").getString(0), out_DomainURL + "ngs/fs/get?file=" + resultJson.getJSONArray("value").getString(0)));
            } else {
                return errorRedirect(response, "文件上传NGS异常", "文件上传NGS结果异常:" + KHUtil.GetSFJV(resultJson, "message"));
            }

        } catch (Exception e) {
            log.error("Excel文件处理异常", e);
            return errorRedirect(response, "Excel文件处理异常", "Excel文件处理异常:" + e.getMessage());
        } finally {
            boolean del_flag = KHFileUtil.deleteDiretory(TempFilesPath + tmp_FileDir);
            log.error("End临时文件删除结果:" + del_flag);

        }

    }

    // 异常&错误重定向
    public static JSONObject errorRedirect(HttpServletResponse response, String ErrorTitle, String ErrorInfo) {
        log.warn("异常&错误[apiDynLogin]重定向 ErrorTitle:" + ErrorTitle + " ErrorInfo:" + ErrorInfo);

        return JSONObject.fromObject(new Result(Result.ERROR, ErrorTitle, ErrorInfo));
    }

    // 工具方法
    public static String getQueryString(Map<String, String> params) {
        String url = "";
        if (params == null) {
            return url;
        }

        StringBuilder builder = new StringBuilder(url);

        int i = 0;
        for (String key : params.keySet()) {
            String value = params.get(key);
            if (value == null) { // 过滤空的key
                continue;
            }

            if (i != 0) {
                builder.append('&');
            }

            builder.append(key);
            builder.append('=');
            builder.append(encode(value));

            i++;
        }

        return builder.toString();
    }

    // 工具方法
    public static String encode(String input) {
        if (input == null) {
            return "";
        }

        try {
            return URLEncoder.encode(input, "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        return input;
    }

    // 获取拼接某个会话聊天历史
    public static String getChat_Msg(EliteBean eliteBean, String session_id) {
        String SQL = '''Select Case
                                 When Length(R1.Chat_User_Id) > 6 Then
                                  '客户:'
                                 When R3.Id Is Not Null Then
                                  '机器人:'
                                 When R2.Staff_Id Is Not Null Then
                                  '客服:'
                                 Else
                                  Concat(R2.Staffname, ':')
                               End As Chat_User_Id,
                               R1.Post_Time,
                               R1.Message_Text,
                               R1.Chat_User_Id
                          From Chat_Message R1
                          Left Join Staff R2
                            On R1.Chat_User_Id = R2.Staff_Id
                          Left Join Robot R3
                            On R1.Chat_User_Id = R3.Bot_Name
                         Where R1.Chat_Session_Id = \'''' + session_id + '''\'
                         Order By 2''';

        List SQLRes = eliteBean.chaxun_sql(SQL, "dataSource");

        StringBuffer to_Msg_Info = new StringBuffer();
        for (int i = 0; i < SQLRes.size(); i++) {
            String Chat_User_Id = SQLRes.get(i).getAt("Chat_User_Id");
            String Post_Time = SQLRes.get(i).getAt("Post_Time");
            String Message_Text = SQLRes.get(i).getAt("Message_Text");

            Message_Text = dealMsg_Text(Message_Text);

            if (KHUtil.isNull(to_Msg_Info.toString())) {
                to_Msg_Info.append(Chat_User_Id + Message_Text);
            } else {
                to_Msg_Info.append(System.lineSeparator() + System.lineSeparator() + Chat_User_Id + Message_Text);

            }

        }

        return to_Msg_Info;
    }

    public static String dealMsg_Text(String Message_Text) {
        Message_Text = StringEscapeUtils.unescapeHtml(Message_Text);
        try {
            if (Message_Text.startsWith("{")) {
                JSONObject MessageBody = JSONObject.fromObject(Message_Text);
                Message_Text = MessageBody.getJSONObject("data").getString("answer");
            } else if (Message_Text.startsWith("<img src=")) {
                Message_Text = Message_Text.replace("<img src=\"", "").replace("\">", "");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return StripHTML(Message_Text);

    }

    public static String StripHTML(String str) {
        //如果有双引号将其先转成单引号
        String htmlStr = str.replaceAll("\"", "'");
        String regEx_script = "<script[^>]*?>[\\s\\S]*?<\\/script>"; // 定义script的正则表达式
        String regEx_style = "<style[^>]*?>[\\s\\S]*?<\\/style>"; // 定义style的正则表达式
        String regEx_html = "<[^>]+>"; // 定义HTML标签的正则表达式

        Pattern p_script = Pattern.compile(regEx_script, Pattern.CASE_INSENSITIVE);
        Matcher m_script = p_script.matcher(htmlStr);
        htmlStr = m_script.replaceAll(""); // 过滤script标签

        Pattern p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE);
        Matcher m_style = p_style.matcher(htmlStr);
        htmlStr = m_style.replaceAll(""); // 过滤style标签

        Pattern p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE);
        Matcher m_html = p_html.matcher(htmlStr);
        htmlStr = m_html.replaceAll(""); // 过滤html标签

        return htmlStr.trim(); //返回文本字符串
    }

    public static boolean downAcctchFiles(String Workorderid, int i, List<Map<String, Object>> attachList) {

        log.info("附件 downAcctchFiles-" + i + " Workorderid:" + Workorderid + " attachList:" + attachList.toString());
        int attach_Seq = 0;
        String old_Class_Desc = "";
        for (int j = 0; j < attachList.size(); j++) {
            Map<String, Object> attachData = attachList.get(j);
            String Path = attachData.get("Path");
            String Filename = attachData.get("Filename");
            String Upload_Class1_Desc = attachData.get("Upload_Class1_Desc");
            String Upload_Class2_Desc = attachData.get("Upload_Class2_Desc");

            // 文件序号处理
            if (old_Class_Desc != Upload_Class1_Desc + "_" + Upload_Class2_Desc) {
                attach_Seq = 0;
                old_Class_Desc = Upload_Class1_Desc + "_" + Upload_Class2_Desc
            }
            attach_Seq = attach_Seq + 1;

            // 生成文件名
            String save_FileName = Upload_Class1_Desc + "_" + Upload_Class2_Desc + "[" + attach_Seq + "]" + KHUtil.getIntRandom(6) + "-" + Filename;
            String save_FileDir = TempFilesPath + tmp_FileDir + "/附件(" + (i + 1) + ")-" + Workorderid + "_v" + tmp_FileDir.replace("工单数据_", "") + "/";
            String save_FilePath = save_FileDir + save_FileName;

            // 临时目录检查&创建
            Util.createDir(save_FileDir);

            // 下载文件
            boolean downFlag = false;
            try {
                downFlag = Util.httpDownLoadFile(in_DomainURL + "ngs/fs/get?file=" + Path, save_FilePath, 12000);
                log.info("附件-" + j + "文件下载成功！...");

            } catch (Exception e) {
                log.error("附件-" + j + "文件下载到服务器-处理异常");
            }

        }
    }

    /**
     * 获取文件拓展名
     *
     * @param file_Path 路径名
     * @return
     */
    public static String fileSuffix(String file_Path) {
        String fileSuffix = "";
        int dotIndex = file_Path.lastIndexOf(".");
        if (dotIndex != -1) {
            fileSuffix = file_Path.substring(dotIndex + 1);
        }

        log.debug("文件拓展名为：" + fileSuffix);

        return fileSuffix;
    }
}